<?php
/**
 * This class represents 'medicine_coefficient' table.
 *
 * @package dept_medicine.core.ordo 
 * @author pktquynh
 * @version $Id:$
 * @copyright 2009 LHV Company
 */
class MedicineUsageStatus extends ORDataObject {

	var $medicine_usage_status_id = '';
    var $medicine_coefficient_id = '';
    var $issue_num = '';
    var $used_num = '';
    var $remain_num = '';
    var $modified = '';
    
    var $_table         = 'medicine_usage_status';
    var $_internalName  = 'MedicineUsageStatus';
    var $_key           = 'medicine_usage_status_id';
    
    public function MedicineCoefficient($id = 0) {
        //call the parent constructor so we have a _db to work with
        parent::ORDataObject();     
    }
    
    /**
     * List all latest usage status of all medicine for some department.
     * @param deptId The id of department
     * @return array of all medicine with usage status
     */
    public function listLastMedicineUsageForDept($deptId, $medTypeId, $medicineId) {
    	$fieldName =   L10nHelper::changeField(L10nHelper::getLocale(), 'name');
    	$fieldQuantityTitle =   L10nHelper::changeField(L10nHelper::getLocale(), 'quantity_title');
        
        $where = " mc.department_id = '" . $deptId . "'";
        if ($medTypeId != 'all') {
        	$where .= " AND m.functional_type = '" . $medTypeId . "'";
        }
        if ($medicineId != 'all') {
        	$where .= " AND mc.medicine_id = '" . $medicineId . "'";
        }
        $ds = new Datasource_sql();
        $sql = array (
            'cols'    => " distinct mc.medicine_coefficient_id as med_coef_id, 
                        et.$fieldName as medicine_type, m.name as medicine_name, 
                        mdu.$fieldQuantityTitle as quantity_title,"
                       . " mus.medicine_usage_status_id, mus.issue_num, mus.used_num, mus.remain_num, mus.modified as latest_date",
            'from'    => " medicine_coefficient mc"
                         . " LEFT JOIN medicine_usage_status mus ON mc.medicine_coefficient_id = mus.medicine_coefficient_id"
                         . " LEFT JOIN medicine m ON mc.medicine_id = m.medicine_id "
                         . " LEFT JOIN equipment_type et ON m.functional_type = et.equipment_type_id"
                         . " LEFT JOIN medicine_default_usage mdu ON m.default_usage_id = mdu.id",                         
            'where'   => $where
                       . " AND (mus.modified is null OR mus.modified IN (
                               SELECT MAX(mus_max.modified)
                               FROM medicine_usage_status mus_max
                               WHERE mus_max.medicine_coefficient_id = mc.medicine_coefficient_id  
                               GROUP BY mus_max.medicine_coefficient_id))",
            'orderby' => "medicine_type, medicine_name");
                         
        $ds->setup($this->_db, $sql);
        $result = $ds->toArray();
    	return $result;
    }
    
   /**
    * List all latest usage status of medicine belong to medicine type with used number, remaining number and requirement number in  
    * a specific department.
    * @param deptId The id of department
    * @param medType The medicine type
    * @return array of all medicine
    */
    public function listLatestMedUsageStatus($deptId, $medType) {
        $fieldName =   L10nHelper::changeField(L10nHelper::getLocale(), 'name');
        $fieldQuantityTitle =   L10nHelper::changeField(L10nHelper::getLocale(), 'quantity_title');
            	
        $medTypeCond = '';
        if ($medType != 'all') {
        	$medTypeCond = " AND m.functional_type = '" . $medType . "'";
        }
        
        $ds = new Datasource_sql();
        $sql = array (
            'cols'    => "'' as id, m.medicine_id,
                        et.$fieldName as medicine_type, m.name as medicine_name,                           
                        mdu.$fieldQuantityTitle as quantity_title,"
                       . " mus.used_num, mus.remain_num, (mc.quantity_num - mus.remain_num) as requirement_num," 
                       . " mus.modified as latest_date",
            'from'    => $this->_table . " mus"
                         . " LEFT JOIN medicine_coefficient mc ON mus.medicine_coefficient_id = mc.medicine_coefficient_id"
                         . " LEFT JOIN medicine m ON mc.medicine_id = m.medicine_id "
                         . " LEFT JOIN equipment_type et ON m.functional_type = et.equipment_type_id"
                         . " LEFT JOIN medicine_default_usage mdu ON m.default_usage_id = mdu.id",                         
            'where'   => "mc.department_id = '" . $deptId . "'"
                       . $medTypeCond
                       . " AND mus.modified IN (
                               SELECT MAX(mus_max.modified)
                               FROM medicine_usage_status mus_max
                               WHERE mus_max.medicine_coefficient_id = mc.medicine_coefficient_id  
                               GROUP BY mus_max.medicine_coefficient_id)",
            'orderby' => "medicine_type, medicine_name");
                         
        $ds->setup($this->_db, $sql);
        $result = $ds->toArray();
        return $result;
    }    
}
?>